Introduction to Table Calculations in Tableau

Topic: Introduction to Table Calculations

Objective: Understand table calculations in Tableau, learn how they work, explore common calculation types, understand addressing and partitioning, and apply table calculations to practical business analysis and dashboards.

1. What Are Table Calculations in Tableau?

Table calculations are calculations that Tableau performs on the values displayed in a visualization. They allow you to analyze data relative to other values in the same view, rather than evaluating each record independently.

For example, you may already have total sales for each month. A table calculation can calculate the cumulative sales from January onward, compare February's sales with January's sales, calculate each month's percentage of annual sales, or rank months according to sales.

Table calculations are particularly useful for trend analysis, comparisons, rankings, percentage calculations, and running totals.

Example

Suppose a company has the following monthly sales:

The Sales column shows the sales for each individual month. The Running Total column accumulates the sales of the current month and all preceding months.

For example, March's running total is 10,000 + 15,000 + 20,000 = 45,000.

Why Are Table Calculations Important?

• Calculate running totals and cumulative performance.
• Compare current values with previous or next values.
• Calculate percentage growth between periods.
• Rank products, customers, regions, or employees.
• Calculate percent of total.
• Create moving averages to analyze trends.
• Identify the highest and lowest values within a view.
• Build more informative dashboards without manually calculating every result.

2. How Table Calculations Work

Table calculations operate on the data available in the visualization. Tableau uses the dimensions and measures in the view to determine the values to calculate and the order in which the calculation is performed.

For example, if you place MONTH(Order Date) on Rows and SUM(Sales) on Columns, Tableau displays sales by month. Applying a running total to SUM(Sales) transforms the displayed monthly values into cumulative sales.

Important Characteristics

• Table calculations depend on the structure and level of detail of the view.
• They operate on the marks or aggregated values in the visualization.
• The direction of the calculation depends on the selected Compute Using settings.
• They can be configured to calculate across rows, columns, panes, or selected dimensions.
• Changing dimensions, sorting, or filters can change the calculation results.
• They are different from ordinary row-level calculations and level-of-detail expressions.

Important: A table calculation does not simply calculate over every row in the underlying data source. It works with the values represented in the view, according to the selected addressing and partitioning settings.

3. Table Calculations vs. Basic Calculated Fields vs. LOD Expressions

Examples

Basic calculated field:

[Sales] - [Cost]

Table calculation:

RUNNING_SUM(SUM([Sales]))

LOD expression:

{ FIXED [Region] : SUM([Sales]) }

Use the calculation type that matches the business question. For example, use a running sum to accumulate monthly sales, but use an LOD expression when you need a region-level total independent of the dimensions displayed in the view.

4. Types of Table Calculations

Tableau provides several common table calculations, including quick table calculations that can be applied through a field's menu.

5. Understanding Quick Table Calculations

Quick table calculations allow you to apply commonly used table calculations without manually writing a formula. Tableau supplies a typical configuration, which you can customize if needed.

How to Apply a Quick Table Calculation

1. Open a worksheet containing a measure, such as Sales.
2. Place a dimension, such as Month of Order Date, in the view.
3. Place SUM(Sales) in the visualization.
4. Right-click the measure in the view or open its drop-down menu.
5. Select Quick Table Calculation.
6. Choose Running Total, Difference, Percent Difference, Percent of Total, Rank, Moving Average, or another available option.
7. Review the updated visualization.
8. If the result is unexpected, open Edit Table Calculation and review Compute Using.

Example: Percent of Total

If a product contributes 25,000 in sales and the total sales represented by the calculation are 100,000, its percent of total is 25%.

Quick table calculations provide a convenient starting point, but you must verify the calculation direction and scope to ensure the result answers the intended question.

6. Running Total

A running total is a cumulative calculation. It adds the current value to all preceding values in the selected calculation direction.

Formula

RUNNING_SUM(SUM([Sales]))

Example

How to Create a Running Total

1. Place MONTH(Order Date) on Rows.
2. Drag Sales to the view and display SUM(Sales).
3. Right-click SUM(Sales).
4. Select Quick Table Calculation and then Running Total.
5. Check whether the running total moves in the intended direction.
6. Open Edit Table Calculation to adjust Compute Using if necessary.

When to Use Running Total

• Monthly cumulative revenue.
• Year-to-date sales tracking.
• Cumulative orders received.
• Accumulated profit across time.
• Tracking progress toward a target.

7. Difference Calculation

The Difference table calculation compares the current value with another value in the partition. The default comparison is commonly the previous value.

Formula

SUM([Sales]) - LOOKUP(SUM([Sales]), -1)

This expression subtracts the previous value from the current value, based on the calculation's configured direction.

Example

January has no preceding month in this example, so its difference is unavailable. February increased by 2,000, March decreased by 3,000, and April increased by 6,000.

Business Applications

• Compare monthly revenue.
• Measure changes in customer orders.
• Compare quarterly profit.
• Identify sudden increases or decreases in sales.

8. Percent Difference Calculation

Percent Difference calculates the change between the current value and a comparison value as a percentage. It is useful for measuring growth or decline over time.

Formula

(SUM([Sales]) - LOOKUP(SUM([Sales]), -1))
/ ABS(LOOKUP(SUM([Sales]), -1))

This example divides the change by the absolute value of the previous result. Format the field as a percentage. The formula should be adapted when negative comparison values or zero values have a special business meaning.

Example

Calculation

February percent difference = (12,000 − 10,000) ÷ 10,000 × 100 = 20%.

March percent difference = (9,000 − 12,000) ÷ 12,000 × 100 = -25%.

April percent difference = (15,000 − 9,000) ÷ 9,000 × 100 ≈ 66.67%.

Note: Tableau's built-in Percent Difference quick table calculation can be used instead of writing this formula manually.

9. Percent of Total

Percent of Total calculates how much each value contributes to the total within the selected partition.

Formula

SUM([Sales]) / TOTAL(SUM([Sales]))

The TOTAL function computes the total of the expression over the relevant partition, based on the table calculation settings.

Example

How to Apply It

1. Place Category on Rows.
2. Place SUM(Sales) in the view.
3. Right-click SUM(Sales).
4. Choose Quick Table Calculation and Percent of Total.
5. Format the result as a percentage if needed.
6. Review Compute Using to ensure that the denominator is the intended total.

Business application: Identify the categories, products, or regions that contribute the largest percentage of revenue.

10. Rank Calculation

The Rank table calculation assigns a rank to each value within a partition. It is useful for ordering products, customers, sales representatives, and regions by performance.

Formula

RANK(SUM([Sales]))

Example

The example assumes descending rank, with the highest sales receiving rank 1.

Related Rank Functions

• RANK(expression) assigns ranks and handles ties according to the selected rank method.
• RANK_DENSE(expression) assigns consecutive ranks without gaps after ties.
• RANK_UNIQUE(expression) assigns unique ranks even when values are tied, using the calculation's ordering rules.
• RANK_PERCENTILE(expression) returns a percentile-style rank.

Business applications: Top-selling products, highest-revenue customers, best-performing regions, and employee performance comparisons.

11. Moving Average

A moving average calculates an average over a selected range of values around the current value. It is often used to reduce short-term fluctuations and reveal longer-term trends.

Example Formula

WINDOW_AVG(SUM([Sales]), -2, 0)

This calculates the average of the current value and the two preceding values within the configured partition.

Example

For April, the average is (12,000 + 14,000 + 16,000) ÷ 3 = 14,000.

At the beginning of a series, fewer than three preceding/current values may be available. The result depends on the window boundaries and calculation configuration.

Business Applications

• Monitor sales trends.
• Reduce the impact of one unusually high or low month.
• Analyze website traffic trends.
• Review recurring demand patterns.

12. Understanding Addressing and Partitioning

Addressing and partitioning are fundamental concepts in table calculations. They determine which values are calculated together and the direction in which Tableau moves through those values.

Addressing

Addressing defines the direction or sequence in which a table calculation is performed. For example, a running total may move down the rows from January through December.

Partitioning

Partitioning defines the groups of values within which a table calculation operates independently. For example, a running total can restart at January for each year rather than continuing from the previous year.

Example

In this example, Month is the addressing dimension and Year separates the data into partitions. The running total restarts for each year.

Easy Way to Remember

• Addressing: Which values should Tableau move through?
• Partitioning: Where should Tableau start a separate calculation?

13. Compute Using Options

Compute Using tells Tableau how to traverse the dimensions in the visualization when evaluating a table calculation.

Available options depend on the structure of the view. Always confirm the highlighted computation path and resulting values when changing Compute Using.

14. Creating a Table Calculation Using a Formula

You can create a table calculation as a calculated field rather than selecting a quick table calculation.

Steps

1. Open a worksheet in Tableau.
2. Right-click in the Data pane and select Create Calculated Field.
3. Enter a descriptive name, such as Running Total of Sales.
4. Enter a table calculation formula.
5. Click Apply or OK after the calculation validates.
6. Drag the calculated field into the visualization.
7. Review its Compute Using settings to ensure the calculation follows the correct direction.

Formula Examples

Running total of sales:

RUNNING_SUM(SUM([Sales]))

Running average of sales:

RUNNING_AVG(SUM([Sales]))

Difference from previous value:

SUM([Sales]) - LOOKUP(SUM([Sales]), -1)

Percent of total:

SUM([Sales]) / TOTAL(SUM([Sales]))

Rank of sales:

RANK(SUM([Sales]))

Moving average of current and two previous values:

WINDOW_AVG(SUM([Sales]), -2, 0)

Table calculation formulas depend on the dimensions and measures in the view. A valid formula may produce an unexpected result if its addressing or partitioning is incorrect.

15. Practical Example: Monthly Sales Analysis

Business requirement: A sales manager wants to see monthly sales, cumulative sales, month-over-month difference, and each month's contribution to the total.

Step 1: Connect to a Dataset

Use a dataset that contains Order Date and Sales. Tableau's Sample - Superstore dataset is commonly used for practice.

Step 2: Create a Monthly Sales View

1. Drag Order Date to Columns or Rows.
2. Change the date level to Month and ensure the year is included if the dataset spans multiple years.
3. Drag Sales into the view as SUM(Sales).
4. Sort the dates chronologically.

Step 3: Add a Running Total

Apply Quick Table Calculation and choose Running Total. Verify that it moves through the months in chronological order.

Step 4: Add Month-over-Month Difference

Use a second worksheet or duplicate the existing worksheet. Apply the Difference quick table calculation to SUM(Sales).

Step 5: Add Percent of Total

Use another worksheet and apply Percent of Total. Check whether the calculation is intended to represent each month's contribution to the selected year or the entire view.

Step 6: Build a Dashboard

• Monthly sales trend chart.
• Running total line chart.
• Month-over-month change chart.
• Percent-of-total breakdown.
• Filters for year, region, and category.

Expected outcome: The manager can review overall revenue, cumulative progress, month-to-month changes, and the relative contribution of each month.

16. Practical Example: Product Ranking

Business requirement: Rank products based on total sales and identify the best-performing products.

Steps

1. Drag Product Name to Rows.
2. Drag Sales to Columns.
3. Sort products by SUM(Sales) in descending order.
4. Apply Rank as a quick table calculation to SUM(Sales).
5. Review Compute Using to ensure the rank is calculated across the product dimension.
6. Optionally filter the view to show a smaller selection of products.

Calculated Field

RANK(SUM([Sales]))

Business value: Sales teams can identify top-selling products and investigate products with lower sales.

Note: A rank table calculation ranks the values in the view. If the view contains additional dimensions, configure partitioning so that products are ranked within the intended group, such as Region or Category.

17. Practical Example: Year-over-Year Growth

Business requirement: Compare sales across years to determine whether the business is growing or declining.

Approach

1. Place YEAR(Order Date) in the view.
2. Place SUM(Sales) in the view.
3. Sort years chronologically.
4. Apply Percent Difference using the previous value, or use a suitable year-over-year quick table calculation.
5. Check the direction and partitioning of the calculation.

Example Formula for Adjacent Values

(SUM([Sales]) - LOOKUP(SUM([Sales]), -1))
/ ABS(LOOKUP(SUM([Sales]), -1))

This formula compares the current value with the previous value in the configured sequence. If the view has one mark per year, the previous value represents the preceding displayed year. If the view includes months or other dimensions, adjust the calculation settings to compare the intended periods.

Important: The first period has no previous value. If the previous value is zero, the percentage calculation requires special handling to avoid division by zero.

18. Practical Example: Moving Average for Sales Trends

Business requirement: A company wants to smooth monthly sales fluctuations to see the general trend.

Formula

WINDOW_AVG(SUM([Sales]), -2, 0)

Steps

1. Place Month of Order Date in the view.
2. Place SUM(Sales) in the view.
3. Create the moving-average calculated field.
4. Drag the field into the view alongside sales.
5. Set Compute Using to follow the chronological order of months.
6. Compare the original sales series with the moving-average series.

Business value: The moving average can help highlight longer-term patterns without removing the original sales information.

19. Table Calculation Functions

Tableau includes functions that allow you to customize calculations beyond the quick table calculation menu.

The exact results of these functions depend on the view, partition, addressing, sort order, and any specified window boundaries.

20. Common Mistakes in Table Calculations

21. Best Practices for Table Calculations

• Understand the business question before choosing a table calculation.
• Use chronological sorting for time-based calculations.
• Check Compute Using whenever results look unexpected.
• Understand the difference between addressing and partitioning.
• Test calculations using a small, readable text table before building a complex chart.
• Validate running totals against the sum of the underlying displayed values.
• Check the treatment of ties in ranking calculations.
• Handle zero and NULL values in percentage calculations.
• Recheck calculations after adding or removing dimensions.
• Use descriptive names for calculated fields.
• Keep the original measure available when comparing it with a calculated result.
• Use the Tableau calculation editor to verify formula syntax.

22. Practice Exercises

Beginner Exercises

1. Create a running total of monthly sales.
2. Calculate the difference between the current month and the previous month.
3. Calculate the percentage contribution of each product category to total sales.
4. Rank products by total sales.
5. Calculate a moving average using the current month and the two preceding months.

Intermediate Exercises

1. Create a running total that restarts for each year.
2. Calculate monthly sales growth and identify months with declining sales.
3. Rank customers within each region by sales.
4. Calculate the percentage of total sales within each product category.
5. Compare the effect of Table (Down) and Table (Across) on a two-dimensional table.

Advanced Exercises

1. Create a dashboard containing monthly sales, running totals, and percent differences.
2. Use Specific Dimensions to control addressing and partitioning explicitly.
3. Compare a table calculation with a FIXED LOD expression and explain how their results differ.
4. Build a year-over-year sales comparison and handle the first year appropriately.
5. Investigate how changing filters affects a running total or percent-of-total calculation.

Sample Answers

1. Running total:

RUNNING_SUM(SUM([Sales]))

2. Difference from previous value:

SUM([Sales]) - LOOKUP(SUM([Sales]), -1)

3. Percent of total:

SUM([Sales]) / TOTAL(SUM([Sales]))

4. Rank:

RANK(SUM([Sales]))

5. Three-value moving average:

WINDOW_AVG(SUM([Sales]), -2, 0)

23. Interview Questions and Answers

Q1. What is a table calculation in Tableau?

Answer: A table calculation transforms values in a visualization. Examples include running totals, rankings, moving averages, differences, and percent of total.

Q2. How is a table calculation different from a basic calculated field?

Answer: A basic calculated field derives values from fields in the data. A table calculation operates on the values represented in the view and depends on the view's structure.

Q3. What is a quick table calculation?

Answer: It is a convenient way to apply a common table calculation through the field menu without manually writing the formula.

Q4. What is a running total?

Answer: A running total accumulates values from the beginning of a partition to the current value.

Q5. What is the difference between Difference and Percent Difference?

Answer: Difference returns the numerical change between two values. Percent Difference expresses that change relative to the comparison value as a percentage.

Q6. What does Percent of Total calculate?

Answer: It calculates the proportion of a value relative to the total defined by the current calculation partition.

Q7. What is addressing?

Answer: Addressing determines the direction or sequence through which Tableau evaluates values in a table calculation.

Q8. What is partitioning?

Answer: Partitioning divides the view into separate groups in which the table calculation is evaluated independently.

Q9. What does Compute Using do?

Answer: Compute Using controls the dimensions and direction used to evaluate a table calculation, such as Table Across, Table Down, or Specific Dimensions.

Q10. What is a moving average?

Answer: A moving average calculates an average across a specified range of values around the current value, often to smooth short-term fluctuations.

Q11. Why might a running total be incorrect?

Answer: The calculation may be moving in the wrong direction, using an incorrect partition, or following an unintended sort order.

Q12. Why can adding a dimension change the result?

Answer: Adding a dimension changes the view's level of detail and may alter the addressing and partitioning of the calculation.

Q13. Can table calculations be used in dashboards?

Answer: Yes. They can support trend charts, ranking views, cumulative KPIs, percentage comparisons, and other dashboard visualizations.

Q14. What is the purpose of LOOKUP?

Answer: LOOKUP returns a value from a relative position in the current partition. For example, LOOKUP(expression, -1) references the preceding value in the configured sequence.

Q15. What is the difference between a table calculation and an LOD expression?

Answer: Table calculations work on values in the view. LOD expressions specify the level at which an expression is computed using dimensions in the LOD expression. They serve different purposes and follow different calculation rules.

24. Quick Revision Checklist

• Understand what table calculations are and why they are useful.
• Know the difference between basic calculations, table calculations, and LOD expressions.
• Apply quick table calculations from a measure's menu.
• Create running totals, differences, percent differences, and percent of total.
• Use rank and moving-average calculations.
• Understand addressing and partitioning.
• Configure Compute Using correctly.
• Verify sort order and partition boundaries.
• Understand how dimensions and filters can affect results.
• Validate calculations using small example tables.

25. Conclusion

Table calculations are powerful tools for analyzing values in Tableau visualizations. They make it possible to calculate cumulative totals, measure changes, evaluate percentages, rank performance, and smooth trends without manually calculating every result outside Tableau.

To use them effectively, understand how the view is structured, choose the appropriate calculation, and configure addressing and partitioning to match the business question. Always verify the results after changing dimensions, filters, or sort order.

26. Learn Tableau with JustAcademy

Explore Tableau training and register for a course demo using the links below.

• JustAcademy Tableau Training
• Register for a Tableau Course Demo

Official Tableau Learning Resources

• Transform Values with Table Calculations
• Quick Table Calculations
• Table Calculation Functions